package edu.tms.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import edu.tms.bean.SchoolLife;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 学生个人展示板块表dao
 * @author Administrator
 *
 */
public class SchoolLifeDaoImp implements CommnonDao<SchoolLife>{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(SchoolLife value) throws Exception {
		String sql="insert into t_school_life value (null,?,?,?,?,?)";
		SchoolLife sl=(SchoolLife)value;
		return JdbcUtil.insertAuto(sql,
					sl.getStuId(),
					sl.getAlbumId(),
					sl.getSchoolLifeContent(),
					sl.getCreateTime());
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(SchoolLife value) throws Exception {
		String sql="update t_school_life set  stu_id=?,album_id=?,school_life_content=?,"
				+ "create_time=?,exhibition_state=? where school_life_id=?";
		SchoolLife sl=(SchoolLife)value;
		return JdbcUtil.update(sql,
					sl.getStuId(),
					sl.getAlbumId(),
					sl.getSchoolLifeContent(),
					sl.getCreateTime(),
					sl.getSchoolLifeId());
	}
	
	/**
	 * 删除内容
	 * 相册表t_album，学生表t_teacher
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(SchoolLife value) throws Exception {
		String sql="delete  t_picture,t_school_life,t_album from t_class_activity "
				+"left JOIN t_picture on  t_class_activity.album_id=t_picture.album_id" 
				+"left JOIN t_album on  t_class_activity.album_id=t_picture.album_id "
				+"where t_school_life.school_life_id=?; ";
		SchoolLife sl=(SchoolLife)value;
		return JdbcUtil.update(sql, sl.getSchoolLifeId());
	}
	
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SchoolLife> findAll(int pageNo, int pageSize) throws Exception {
		String sql ="select * from t_school_life as sl ,t_student as stu,t_album as al "
				+ "where sl.stu_id=stu.stu_id and sl.album_id=al.album_id limit ?,?";
		return JdbcUtil.query(SchoolLife.class,sql,(pageNo-1)*10,pageSize);
	}
	
	/**
	 * 查找单一实体
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public SchoolLife findByKey(Object Key) throws Exception {
		return null;
	}

	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<SchoolLife> findAll(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="select * from t_school_life as sl ,t_student as stu,t_album as al "
				+ "where sl.stu_id=stu.stu_id and sl.album_id=al.album_id"
				+ " or sl.school_life_id=? or sl.stu_id=? or sl.album_id=? or sl.create_time=? "
				+ " or sl.exhibition_state=?"
				+ " limit  ?,?";
		List<SchoolLife> list=new ArrayList<SchoolLife>();
		list =JdbcUtil.query(SchoolLife.class,sql,Key,Key,Key,Key,Key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * ZL
	 * 校园历程所用
	 * @param Key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<SchoolLife> findByid(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="SELECT * FROM t_school_life WHERE stu_id=? or activity_title=? "
				+ "limit  ?,?";
		List<SchoolLife> list=new ArrayList<SchoolLife>();
		list =JdbcUtil.query(SchoolLife.class,sql,Key,Key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * ZL
	 * 校园历程所用
	 * @param Key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<SchoolLife> find(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="SELECT * FROM t_school_life WHERE stu_id=? or activity_title=? "
				+ "limit  ?,?";
		List<SchoolLife> list=new ArrayList<SchoolLife>();
		list =JdbcUtil.query(SchoolLife.class,sql,Key,Key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * 金礼超
	 * 保存空相册
	 * @param value
	 * @throws Exception
	 */
	public long saveAblum(String title,Integer id) throws Exception {  
		String sql="INSERT INTO t_album(album_name,album_url,create_time)"
				+ "VALUES(?,?,now())";
		return JdbcUtil.insertAuto(sql,title+id,"download/"+title+id);
	}
	
	/**
	 * 邹磊
	 * 辅导员查询所有校园历程
	 * @param Key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<SchoolLife> findAllUid(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="SELECT tsl.*,tci.class_name,ts.stu_name  "
				+ "FROM t_school_life tsl,t_student ts ,t_class_info tci   "
				+ "WHERE tsl.stu_id=ts.stu_id AND tci.class_name=ts.stu_class_name   "
				+ "AND ts.stu_class_name=(SELECT class_name FROM t_class_info WHERE class_tch_id=? LIMIT 1)  LIMIT ?,?";
		List<SchoolLife> list=new ArrayList<SchoolLife>();
		list =JdbcUtil.query(SchoolLife.class,sql,Key,pageNo,pageSize);
		return list;
	}
	
	/**
	 * 邹磊
	 * 辅导员查询所有校园历程总数
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public Long findAllUidCount(Object Key) throws Exception {
		String sql ="SELECT COUNT(*)  "
				+ "FROM t_school_life tsl,t_student ts ,t_class_info tci   "
				+ "WHERE tsl.stu_id=ts.stu_id AND tci.class_name=ts.stu_class_name   "
				+ "AND ts.stu_class_name=(SELECT class_name FROM t_class_info WHERE class_tch_id=? LIMIT 1)";
		return (Long) JdbcUtil.uniqueValue(sql, 1,Key );
	}
	
	/**
	 * 邹磊
	 * 辅导员查询校园历程 可根据班级名称 和学生名称
	 * @param Key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<SchoolLife> findByCondition(Object Key,Integer stuId, int pageNo, int pageSize) throws Exception{
		String sql ="SELECT tsl.*,tci.class_name,ts.stu_name   "
				+ "FROM t_school_life tsl,t_student ts ,t_class_info tci  "
				+ "WHERE tsl.stu_id=ts.stu_id AND tci.class_name=ts.stu_class_name  "
				+ "AND ts.stu_class_name=? AND tci.class_tch_id=? OR ts.stu_name=?  LIMIT ?,?";
		List<SchoolLife> list=new ArrayList<SchoolLife>();
		list =JdbcUtil.query(SchoolLife.class,sql,Key,stuId,Key,pageNo,pageSize);
		return list;
	}
	
	/**
	 * 邹磊
	 * 辅导员查询校园历程 可根据班级名称 和学生名称
	 * @param Key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public Long findByConditionCount(Object Key,Integer stuId) throws Exception{
		String sql ="SELECT COUNT(*)    "
				+ "FROM t_school_life tsl,t_student ts ,t_class_info tci  "
				+ "WHERE tsl.stu_id=ts.stu_id AND tci.class_name=ts.stu_class_name  "
				+ "AND ts.stu_class_name=? AND tci.class_tch_id=? OR ts.stu_name=? ";
		return (Long) JdbcUtil.uniqueValue(sql, 1,Key, stuId,Key);
	}
	
	/**
	 * 邹磊
	 * 查询单条历程信息
	 * @param aid
	 * @return
	 * @throws Exception
	 */
	public SchoolLife findBySid(Integer sid) throws Exception{
		String sql = "SELECT * FROM  t_school_life  WHERE  school_life_id=?";
		return (SchoolLife) JdbcUtil.uniqueBean(SchoolLife.class, sql, sid);
	}
}
